

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    
    <title>Managing Data Acquisition (with Excel) &mdash; RWC Converter v1.0 documentation</title>
    <link rel="stylesheet" href="_static/default.css" type="text/css" />
    <link rel="stylesheet" href="_static/pygments.css" type="text/css" />
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '',
        VERSION:     '1.0',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true
      };
    </script>
    <script type="text/javascript" src="_static/jquery.js"></script>
    <script type="text/javascript" src="_static/underscore.js"></script>
    <script type="text/javascript" src="_static/doctools.js"></script>
    <link rel="top" title="RWC Converter v1.0 documentation" href="index.html" />
    <link rel="prev" title="Using Django with a Database Server" href="django_dbserver_gui.html" /> 
  </head>
  <body>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="General Index"
             accesskey="I">index</a></li>
        <li class="right" >
          <a href="django_dbserver_gui.html" title="Using Django with a Database Server"
             accesskey="P">previous</a> |</li>
        <li><a href="index.html">RWC Converter v1.0 documentation</a> &raquo;</li> 
      </ul>
    </div>  

    <div class="document">
      <div class="documentwrapper">
        <div class="bodywrapper">
          <div class="body">
            
  <div class="section" id="managing-data-acquisition-with-excel">
<h1>Managing Data Acquisition (with Excel)<a class="headerlink" href="#managing-data-acquisition-with-excel" title="Permalink to this headline">¶</a></h1>
<p>Excel, while a powerful tool, is not the best solution to many problems.
The mass collection of data for components in the ATR plant systems is a large
project that will require several man-years of work.  This work demand will require
a combined effort of tens of Engineers if it is to be accomplished in a reasonable time period.
Maintaining data integrity and consistency is at least as important as comprehensive coverage of
the plant.</p>
<p>Excel is not designed to be a collaborative tool, and must be used with extreme care in order to maintain
data integrity. Excel is also unable to properly handle data structures of any significant
complexity (complexity of the kind involved in this project). Following are sections that discuss
Excel&#8217;s limitations and protective measures that can be taken to reduce problem frequency.</p>
<div class="section" id="excel-is-flat">
<h2>Excel is Flat<a class="headerlink" href="#excel-is-flat" title="Permalink to this headline">¶</a></h2>
<p>Some fields for a single component contain multiple instances of the field data type.  For example,
the &#8216;Drawing&#8217; field for a component may have several drawings listed.
For the sake of this document, these kinds of fields will be called multi-fields.
A table follows illustrating this concept:</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="38%" />
<col width="62%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head">Tag ID</th>
<th class="head">Drawings</th>
</tr>
</thead>
<tbody valign="top">
<tr><td>670-M-10</td>
<td>123456; 234567; 345678</td>
</tr>
<tr><td>670-M-11</td>
<td>334567; 445678</td>
</tr>
</tbody>
</table>
</div></blockquote>
<p>Where &#8216;123456&#8217; is an instance of the &#8216;Drawing&#8217; data type (as is &#8216;234567&#8217;, etc.).
670-M-10 has three instances of the &#8216;Drawing&#8217; data type.
670-M-11 has two instances of the &#8216;Drawing&#8217; data type.
The &#8216;Drawings&#8217; title is a multi-field.  Notice the distinction between &#8216;Drawings&#8217; field title
and the &#8216;Drawing&#8217; data type. The &#8216;Drawings&#8217; multi-field can contain multiple instances of the
&#8216;Drawing&#8217; data type.</p>
<p>The natural data structure for a component is tree-shaped and not flat.
Excel, as a glorified table, can only handle tree-shaped data via work-arounds.
There are a few fields that I have identified as multi-fields:</p>
<blockquote>
<div><ul class="simple">
<li>Drawings</li>
<li>Flows (To and From): Mechanical, Electrical, and I &amp; C</li>
<li>Picture Names</li>
</ul>
</div></blockquote>
<p>All fields that have plural titles are potential multi-fields.
The simplest (and best in this case) work-around is to separate instances in a multi-field with a designated delimiter.
Storing data using this method allows the placement of
multiple instances of a data type in a single field or cell. Safely using this delimited approach has two requirements:</p>
<blockquote>
<div><ol class="arabic simple">
<li>An appropriate delimiter must be selected and must be the only delimiter used for all data in the multi-field.
The delimiter must <strong>not</strong> occur in any instance of the multi-field&#8217;s data type.</li>
<li>Avoid extraneous data in the field (including tabs and returns).
The <strong>only</strong> things existing in the field are data type instances and the delimiter.</li>
</ol>
</div></blockquote>
<p>Three examples of <strong>incorrect</strong> data entry are shown below.
&#8216;Flows&#8217; is the multi-field used in these examples.</p>
<blockquote>
<div><ul>
<li><p class="first">Violates rule #1 (comma delimiter occurs in &#8216;Flow&#8217; data type instance)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Flows (upstream)</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">670-M-11,A, 670-M-9</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11,A</p>
</td>
<td><p class="first last">670-M-10</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
<li><p class="first">Violates rule #1 (inconsistent delimiters used)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Drawings</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">123456, 234567; 345678</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11</p>
</td>
<td><p class="first last">334567; 445678</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
<li><p class="first">Violates rule #2 (&#8216;heat exchanger&#8217; text is not part of &#8216;Flow&#8217; data type)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Flows (upstream)</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">670-M-11,A; 670-M-9</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11,A</p>
</td>
<td><p class="first last">670-M-10 (heat exchanger)</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
</ul>
</div></blockquote>
</div>
<div class="section" id="single-user-documents">
<h2>Single User Documents<a class="headerlink" href="#single-user-documents" title="Permalink to this headline">¶</a></h2>
<ul class="simple">
<li>on spreading the workload</li>
</ul>
<p>Although Excel provides functionality for multiple users to view/edit a workbook
at the same time, this feature is notorious for being unstable and prone to losing data.
I strongly recommend against it.</p>
<p>My recommendation for managing data in single-user-document setting involves the following:</p>
<blockquote>
<div><ul>
<li><p class="first">A master workbook must be maintained.</p>
</li>
<li><p class="first">Generally, data acquisition and construction should take place external to this master workbook.</p>
</li>
<li><p class="first">Completed records will be periodically submitted to the master.</p>
</li>
<li><dl class="first docutils">
<dt>A system/procedures for:</dt>
<dd><ul class="first last simple">
<li>handling corrections to records on the master</li>
<li>identifying duplicate records on the master</li>
<li>Periodic <a class="reference internal" href="#backups"><em>Soft and hard backups</em></a>.</li>
</ul>
</dd>
</dl>
</li>
</ul>
</div></blockquote>
<p>This kind of master-child system has great potential for failure if not designed and executed properly.
Anticipating problems will be very important.
Two problems more likely to be encountered are identified below (with potential causes and possible solutions).
This sort of analysis should be a vital part of designing the data acquisition process:</p>
<blockquote>
<div><table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Problem:</th><td class="field-body"><table class="first docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Description:</th><td class="field-body"><p class="first">Duplicate Records</p>
</td>
</tr>
<tr class="field"><th class="field-name">Discovery:</th><td class="field-body"><p class="first">Search for records that have exactly matching data in one or more fields (e.g. Tag Number)</p>
</td>
</tr>
<tr class="field"><th class="field-name">Causes:</th><td class="field-body"><ul class="first simple">
<li>Record changed after initial submission to master and was resubmitted.</li>
<li>Record(s) submitted twice unknowingly.</li>
</ul>
</td>
</tr>
<tr class="field"><th class="field-name">Solutions:</th><td class="field-body"><ol class="first last arabic simple">
<li>(damage control) Periodic search for duplicate records.</li>
<li>(preventative) Control ID numbers unique to the data acquisition process.
Submitters must acquire ID numbers for each record submitted to the master.
For changes, a submitter must first acquire a copy of the record from the
master workbook with its corresponding control ID. All submissions without a
control ID will be rejected.</li>
</ol>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr class="field"><th class="field-name">Problem:</th><td class="field-body"><table class="first last docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Description:</th><td class="field-body">Relevant data in record fields is missing.</td>
</tr>
<tr class="field"><th class="field-name">Discovery:</th><td class="field-body">Submitter (or other) notices data missing from master.</td>
</tr>
<tr class="field"><th class="field-name">Causes:</th><td class="field-body">Resubmitted a change without including data from current state on master.</td>
</tr>
<tr class="field"><th class="field-name">Solutions:</th><td class="field-body">See solution 2 above.</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
<p>If unchecked, these (and others) are not problems that might occur - they <em>will</em> occur. A good process architecture can
prevent many problems.</p>
</div>
<div class="section" id="change-tracking-and-backups">
<span id="backups"></span><h2>Change-Tracking and Backups<a class="headerlink" href="#change-tracking-and-backups" title="Permalink to this headline">¶</a></h2>
<p>With the distributed data collection that will be occuring, some form of change-tracking will
be important. This can be as simple as making a nightly copy of the master Excel workbook and placing
archiving it away for future reference (known as a soft backup).
Change tracking allows for some consequence mitigation of human failures.
The time required to gather the data is very valuable.  The master Excel workbook should have at least
one backup of itself on separate computer hardware, protecting against hardware failure. This backup
should be updated regularly (known as a hard backup).</p>
<p>Although it will be difficult to protect against human and hardware failures before the data reaches the master
hub, data collectors should be encouraged to make both hard and soft backups as they assemble data
in the stages leading to submission to the master.</p>
</div>
<div class="section" id="handling-pictures">
<h2>Handling Pictures<a class="headerlink" href="#handling-pictures" title="Permalink to this headline">¶</a></h2>
<p>My (strong) recommendations:</p>
<blockquote>
<div><ul>
<li><dl class="first docutils">
<dt>Every picture must be given a unique filename</dt>
<dd><ul class="first last simple">
<li>Although the name can be made to be something like &#8216;670-M-10_pic1.jpg&#8217; for convenience,
it is quite arbitrary.</li>
</ul>
</dd>
</dl>
</li>
<li><p class="first">All pictures (for the entire plant) should be stored in a single folder.</p>
</li>
<li><dl class="first docutils">
<dt>Picture filenames should only include:</dt>
<dd><ul class="first last simple">
<li>alpha-numeric characters (i.e. &#8216;abcdef...&#8217; and &#8216;0123&#8217;)</li>
<li>underscore: &#8216;_&#8217;</li>
<li>dash: &#8216;-&#8216;</li>
</ul>
</dd>
</dl>
</li>
<li><p class="first">Only store the filenames of a component&#8217;s pictures in Excel.
Pictures should <strong>not</strong> be stored in Excel workbooks.</p>
</li>
<li><p class="first">In an Excel record, do <strong>not</strong> include file paths with the picture names.
Store it in a separate field if desired.</p>
</li>
</ul>
</div></blockquote>
</div>
</div>
<div class="section" id="conclusions">
<h1>Conclusions<a class="headerlink" href="#conclusions" title="Permalink to this headline">¶</a></h1>
<p>The rate of data collection in this project will require significant oversight in order to maintain good data integrity and consistency.
Whether or not Excel is used, I recommend either dedicating a qualified person to data administration/management or hiring one.
From my experience, it is data becomes inconsistent and degrades very easily without a great deal of management. That management
can take two forms:</p>
<blockquote>
<div><ol class="arabic simple">
<li>A well designed collaborative solution (to be designed and maintained by a Data Administrator)</li>
<li>Oversight of a work-around solution (i.e. Excel) by a busy, frustrated data administrator.</li>
</ol>
</div></blockquote>
<p>I believe that investing time and resources in the former will prove more successful. Although requiring greater resource
and time investment up front, option 1 will allow for higher productivity data acquisition.  Data integrity can also be enforced
and assessed properly. Option 1 would most likely involve a standard database backend (e.g. oracle, MySQL, PostgreSQL, etc.) and
a web-based frontend. Good luck!</p>
</div>


          </div>
        </div>
      </div>
      <div class="sphinxsidebar">
        <div class="sphinxsidebarwrapper">
  <h3><a href="index.html">Table Of Contents</a></h3>
  <ul>
<li><a class="reference internal" href="#">Managing Data Acquisition (with Excel)</a><ul>
<li><a class="reference internal" href="#excel-is-flat">Excel is Flat</a></li>
<li><a class="reference internal" href="#single-user-documents">Single User Documents</a></li>
<li><a class="reference internal" href="#change-tracking-and-backups">Change-Tracking and Backups</a></li>
<li><a class="reference internal" href="#handling-pictures">Handling Pictures</a></li>
</ul>
</li>
<li><a class="reference internal" href="#conclusions">Conclusions</a></li>
</ul>

  <h4>Previous topic</h4>
  <p class="topless"><a href="django_dbserver_gui.html"
                        title="previous chapter">Using Django with a Database Server</a></p>
  <h3>This Page</h3>
  <ul class="this-page-menu">
    <li><a href="_sources/excel_caution.txt"
           rel="nofollow">Show Source</a></li>
  </ul>
<div id="searchbox" style="display: none">
  <h3>Quick search</h3>
    <form class="search" action="search.html" method="get">
      <input type="text" name="q" size="18" />
      <input type="submit" value="Go" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    <p class="searchtip" style="font-size: 90%">
    Enter search terms or a module, class or function name.
    </p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
      <div class="clearer"></div>
    </div>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="General Index"
             >index</a></li>
        <li class="right" >
          <a href="django_dbserver_gui.html" title="Using Django with a Database Server"
             >previous</a> |</li>
        <li><a href="index.html">RWC Converter v1.0 documentation</a> &raquo;</li> 
      </ul>
    </div>
    <div class="footer">
        &copy; Copyright 2011, Robert Carlsen.
      Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
    </div>
  </body>
</html>